import Foundation

private var mp_convert_db_id_key: UInt8 = 0

enum MPConvertibleSqlType : String {
    case unknow = ""
    case mp_text = "TEXT"
    case mp_integer = "INTEGER"
    case mp_double = "DOUBLE"
}

struct MPConvertibleSqlElemInfo : Equatable {
    
    var sqlName : String = ""
    
    var sqlType : MPConvertibleSqlType = .mp_text
    
    var sqlIndex : Int32 = 0
    
    var propertyInfo : MPConvertiblePropertyInfo
    
    static func == (lhs: MPConvertibleSqlElemInfo, rhs: MPConvertibleSqlElemInfo) -> Bool {
        return lhs.sqlType == rhs.sqlType && lhs.sqlName == rhs.sqlName
    }
    
}

struct MPConvertibleSqlModelInfo {
    
    var tableType : Any.Type
    
    var tableName : String
    
    var elems : [MPConvertibleSqlElemInfo] = []
    
    var tableSql : String = ""
    
    var insertSql : String = ""
    
    var updateSql : String = ""
    
}

@objc public enum MPSqlSearchType : Int {
    /// 必要条件
    case requir
    /// 充分条件
    case suffic
}

/// 操作符
@objc public enum MPSqlSearchOperator: Int {
    ///  等于
    case equal
    ///  大于
    case greater
    ///  小于
    case less
    ///  大于或等于
    case greaterOrEqual
    ///  小于或等于
    case lessOrEqual
    ///  不相等
    case noEqual
    /// 模糊查询
    case like
}

/// 查询条件类型
enum MPSqlSearchParamType {
    /// 根据id查询，传入数据库有记录的model
    case id(MPConvertible)
    /// 根据key查询，传入key和value
    case key(String, _ value : String)
}

/// sql查询条件
public struct MPSqlSearchParam {
        
    /// 查询的key
    internal var key : String = ""
    /// 查询的value
    internal var value : String = ""
    /// 查询的操作符
    internal var opeartor : MPSqlSearchOperator
    /// 查询条件类型
    internal var type : MPSqlSearchType
    
    init(_ param: MPSqlSearchParamType, _ opeartor: MPSqlSearchOperator = .equal, _ type: MPSqlSearchType = .requir) {
        self.opeartor = opeartor
        self.type = type
        switch param {
        case .id(let model):
            key = "id"
            if let sql_id = model.mpconvertible_db_sql_id {
                value = "\(sql_id)"
            } else {
               value = ""
            }
        case .key(let _key, let _value):
            key = _key
            value = _value
        }
    }
    
    init(_ key : String, _ value : String) {
        self.opeartor = .equal
        self.type = .requir
        self.key = key
        self.value = value
    }
    
}

/// 查询参数配置
public struct MPSqlSearchCondi {
    
    /// 查询数量
    var limit : UInt = 0
    
    /// 查询条件
    var params : [MPSqlSearchParam] = []
    
}

private var mp_db_map : [String : MPConvertibleSqlModelInfo] = [:]

private let mp_convertible_db_lock : NSLock = NSLock()

public extension MPConvertible {
    
    var mpconvertible_db_sql_id: String? {
        return objc_getAssociatedObject(self, &mp_convert_db_id_key) as? String
    }
    
    @discardableResult
    /// 数据库查询
    /// - Parameters:
    ///   - db: 所属的数据库
    ///   - conditCallBack: 返回查询条件
    /// - Returns: 查询的结果
    static func mp_searchAllData(_ db : MPConvertibleDB = MPConvertibleDB.share, conditCallBack : ((inout MPSqlSearchCondi) -> Void)? = nil) -> [Self] {
        var searchCondi = MPSqlSearchCondi()
        conditCallBack?(&searchCondi)
        let dbModel = mp_dbModel(db)
        var results : [Self] = []
        for dict in db.searchData(dbModel, params: searchCondi.params, limit: searchCondi.limit) ?? [] {
            let model = Self.mp_model(dict)
            objc_setAssociatedObject(model, &mp_convert_db_id_key, dict["mp_sql_id"], .OBJC_ASSOCIATION_COPY_NONATOMIC)
            results.append(model)
        }
        return results
    }
    
    @discardableResult
    /// 查询数据库指定条件的数据条数
    /// - Parameters:
    ///   - db: 所属数据库
    ///   - conditCallBack: 返回查询条件
    /// - Returns: 查询结果
    static func mp_searchAllCount(_ db : MPConvertibleDB = MPConvertibleDB.share, conditCallBack : ((inout MPSqlSearchCondi) -> Void)? = nil) -> Int {
        var searchCondi = MPSqlSearchCondi()
        conditCallBack?(&searchCondi)
        let dbModel = mp_dbModel(db)
        return db.searchDataCount(dbModel, params: searchCondi.params, limit: searchCondi.limit)
    }
    
    @discardableResult
    /// 新增数据
    /// - Parameter db: 所属的数据库
    /// - Returns: 是否新增成功
    func mp_add(_ db : MPConvertibleDB = MPConvertibleDB.share) -> Bool {
        if let sql_id = db.addData(Self.mp_dbModel(db), model: self) {
            objc_setAssociatedObject(self, &mp_convert_db_id_key, sql_id, .OBJC_ASSOCIATION_COPY_NONATOMIC)
            return true
        }
        return false
    }
    
    @discardableResult
    /// 更新数据，如果没有，就新增，如果有，就更新
    /// - Parameter db: 操作的数据库
    /// - Returns: 是否更新成功
    func mp_refresh(_ db : MPConvertibleDB = MPConvertibleDB.share) -> Bool {
//        if let sqlId = self.mpconvertible_db_sql_id {
//            return db.updateData(dbModel, model: self, sql_id: sqlId)
//        } else {
//            if let sql_id = db.addData(dbModel, model: self) {
//                objc_setAssociatedObject(self, &mp_convert_db_id_key, sql_id, .OBJC_ASSOCIATION_COPY_NONATOMIC)
//                return true
//            }
//            return false
//        }
        if let model = self as? NameModel {
            let dbModel = Self.mp_dbModel(MPConvertibleDB.share)
            return MPConvertibleDB.share.execTransaction(["UPDATE \(dbModel.tableName) SET date = \(model.date) WHERE name = '\(model.name)" + "'"])
        }
        return false
    }
    
//    @discardableResult
    /// 删除数据
    /// - Parameter db: 操作的数据库
    /// - Returns: 是否删除成功
//    func mp_delete(_ db : MPConvertibleDB = MPConvertibleDB.share) -> Bool {
//        if let sqlId = self.mpconvertible_db_sql_id {
//           return db.deleteData(Self.mp_dbModel(db), sqlId: sqlId)
//        }
//        return false
//    }
    
    @discardableResult
    /// 重置表结构
    /// - Parameter db: 要操作的数据库
    /// - Returns: 是否重置成功
    static func mp_reset(_ db : MPConvertibleDB = MPConvertibleDB.share) -> Bool {
        return db.resetTable(mp_dbModel(db))
    }
    
    @discardableResult
    /// 强制数据库同步模型
    /// - Parameter db: 要操作的数据库
    /// - Parameter deleteKey: 要删除的key，如果指定列key，那就会按照指定的key来进行删除
    /// - Returns: 是否同步完成
//    static func forceDbSyncModel(_ db : MPConvertibleDB = MPConvertibleDB.share, deleteKeys : [String] = []) -> Bool {
//        /// 查询model的信息
//        let tableName = String(describing: self)
//        var sqlModelInfo = MPConvertibleSqlModelInfo.init(tableType: self, tableName: tableName)
//        var modelInfo : MPConvertibleModelInfo? = mp_modelInfo()
//        var modelElems : [MPConvertibleSqlElemInfo] = []
//        while let _modelInfo = modelInfo {
//            for propertyInfo in _modelInfo.propertyList {
//                var sqlType : MPConvertibleSqlType = .mp_text
//                switch propertyInfo.propertyType {
//                    case is MPConvertibleIntValue.Type, is Bool.Type:
//                        sqlType = .mp_integer
//                    case is MPConvertibleFloatValue.Type, is MPConvertibleDate.Type:
//                        sqlType = .mp_double
//                    default:
//                        sqlType = .mp_text
//                }
//                modelElems.append(MPConvertibleSqlElemInfo(sqlName: propertyInfo.propertyKey, sqlType: sqlType, sqlIndex: 1, propertyInfo: propertyInfo))
//            }
//            modelInfo = _modelInfo.superInfo
//        }
//        /// 查询数据库中的信息
//        var oldElems : [MPConvertibleSqlElemInfo] = db.reloadTableInfo(tableName)
//        /// 判断数据库中是否有这个表
//        if oldElems.count > 0 {
//            /// 需要删除的表
//            var dropElems : [MPConvertibleSqlElemInfo] = []
//            if deleteKeys.count > 0 {
//                /// 如果指定了要删除的字段，那就从数据库中的信息，找到需要删除的信息
//                for deleteKey in deleteKeys {
//                    /// 在数据库中找到匹配的数据，添加进删除数组
//                    if let elem = oldElems.first(where: { _elem in
//                        return _elem.sqlName == deleteKey
//                    }) {
//                        dropElems.append(elem)
//                    }
//                }
//            } else {
//                /// 如果没有指定要删除的字段，那就遍历数据库中的信息，找到要删除的字段
//                for elem in oldElems {
//                    /// 在model信息中找不到的字段就是要删除的字段
//                    if modelElems.first(where: { _elem in
//                        return _elem.sqlName == elem.sqlName
//                    }) == nil {
//                        dropElems.append(elem)
//                    }
//                }
//            }
//            if dropElems.count > 0 {
//                /// 删除多余的字段
//                if db.dropColumn(tableName, elems: dropElems) == false {
//                    return false
//                }
//            }
//            /// 剩下的就只有要增加的字段了
//            /// 再次获取数据库中表的数据
//            oldElems = db.reloadTableInfo(tableName)
//        }
//        /// 需要新增的表
//        var addElems : [MPConvertibleSqlElemInfo] = []
//        if modelElems.count > 0 {
//            /// 表为空，直接按照顺序排序
//            if oldElems.count == 0 {
//                sqlModelInfo.elems = modelElems
//                for (idx, _) in sqlModelInfo.elems.enumerated() {
//                    sqlModelInfo.elems[idx].sqlIndex = Int32(idx + 1)
//                }
//            } else {
//                /// 表不为空，判断是否需要升级表
//                var sortElems : [MPConvertibleSqlElemInfo] = []
//                /// 如果在数据库中没有的字段，就属于要新增的字段
//                for var elem in modelElems {
//                    if let oldElem = oldElems.first(where: { _elem in
//                        return _elem == elem
//                    }) {
//                        /// 如果数据库里面存在，那就按照数据库中的顺序排序
//                        elem.sqlIndex = oldElem.sqlIndex
//                        sortElems.append(elem)
//                    } else {
//                        /// 如果数据库中不存在，那就属于需要新增的字段
//                        addElems.append(elem)
//                    }
//                }
//                sqlModelInfo.elems = sortElems.sorted(by: { elem1, elem2 in
//                    return elem1.sqlIndex < elem2.sqlIndex
//                })
//                for var elem in addElems {
//                    elem.sqlIndex = Int32(sqlModelInfo.elems.count + 1)
//                    sqlModelInfo.elems.append(elem)
//                }
//            }
//        }
//        sqlModelInfo.tableSql = "CREATE TABLE IF NOT EXISTS \(tableName) (id INTEGER PRIMARY KEY AUTOINCREMENT"
//        sqlModelInfo.insertSql = "INSERT INTO \(tableName) ("
//        sqlModelInfo.updateSql = "INSERT OR REPLACE INTO \(tableName) (id, "
//        var insertSufSql : String = ") VALUES (";
//        var updateSufSql : String = ") VALUES (mp_sql_replace_id_key, ";
//        for (index, elem) in sqlModelInfo.elems.enumerated() {
//            sqlModelInfo.tableSql.append(", \(elem.sqlName) \(elem.sqlType.rawValue)")
//            if index == 0 {
//                sqlModelInfo.insertSql.append("'\(elem.sqlName)'")
//                sqlModelInfo.updateSql.append("'\(elem.sqlName)'")
//                insertSufSql.append("?");
//                updateSufSql.append("?");
//            } else {
//                sqlModelInfo.insertSql.append(", '\(elem.sqlName)'")
//                sqlModelInfo.updateSql.append(", '\(elem.sqlName)'")
//                insertSufSql.append(", ?");
//                updateSufSql.append(", ?");
//            }
//        }
//        sqlModelInfo.insertSql.append(insertSufSql)
//        sqlModelInfo.insertSql.append(")")
//        sqlModelInfo.updateSql.append(updateSufSql)
//        sqlModelInfo.updateSql.append(")")
//        sqlModelInfo.tableSql.append(")")
//        /// 如果数据库没有数据，说明没有这个表格，创建表格
//        if oldElems.count == 0 {
//            if db.createTable(sqlModelInfo) == false {
//                return false
//            }
//        }
//        if addElems.count > 0 {
//            if db.addColumn(tableName, elems: addElems) == false {
//                return false
//            }
//        }
//        mp_db_map[tableName] = sqlModelInfo
//        return false
//    }
    
    private static func mp_dbModel(_ db : MPConvertibleDB = MPConvertibleDB.share) -> MPConvertibleSqlModelInfo {
        mp_convertible_db_lock.lock()
        let tableName = String(describing: self)
        if let sqlModelInfo = mp_db_map[tableName] {
            mp_convertible_db_lock.unlock()
            return sqlModelInfo
        }
        var sqlModelInfo = MPConvertibleSqlModelInfo.init(tableType: self, tableName: tableName)
        var modelInfo : MPConvertibleModelInfo? = mp_modelInfo()
        var modelElems : [MPConvertibleSqlElemInfo] = []
        while let _modelInfo = modelInfo {
            for propertyInfo in _modelInfo.propertyList {
                var sqlType : MPConvertibleSqlType = .mp_text
                switch propertyInfo.propertyType {
                    case is MPConvertibleIntValue.Type, is Bool.Type:
                        sqlType = .mp_integer
                    case is MPConvertibleFloatValue.Type, is MPConvertibleDate.Type:
                        sqlType = .mp_double
                    default:
                        sqlType = .mp_text
                }
                modelElems.append(MPConvertibleSqlElemInfo(sqlName: propertyInfo.propertyKey, sqlType: sqlType, sqlIndex: 1, propertyInfo: propertyInfo))
            }
            modelInfo = _modelInfo.superInfo
        }
        let oldElems : [MPConvertibleSqlElemInfo] = db.reloadTableInfo(tableName)
        /// 需要新增的表
        var addElems : [MPConvertibleSqlElemInfo] = []
        if modelElems.count > 0 {
            /// 表为空，直接按照顺序排序
            if oldElems.count == 0 {
                sqlModelInfo.elems = modelElems
                for (idx, _) in sqlModelInfo.elems.enumerated() {
                    sqlModelInfo.elems[idx].sqlIndex = Int32(idx + 1)
                }
            } else {
                /// 表不为空，判断是否需要升级表
                var sortElems : [MPConvertibleSqlElemInfo] = []
                /// 如果在数据库中没有的字段，就属于要新增的字段
                for var elem in modelElems {
                    if let oldElem = oldElems.first(where: { _elem in
                        return _elem == elem
                    }) {
                        /// 如果数据库里面存在，那就按照数据库中的顺序排序
                        elem.sqlIndex = oldElem.sqlIndex
                        sortElems.append(elem)
                    } else {
                        /// 如果数据库中不存在，那就属于需要新增的字段
                        addElems.append(elem)
                    }
                }
                sqlModelInfo.elems = sortElems.sorted(by: { elem1, elem2 in
                    return elem1.sqlIndex < elem2.sqlIndex
                })
                for var elem in addElems {
                    elem.sqlIndex = Int32(sqlModelInfo.elems.count + 1)
                    sqlModelInfo.elems.append(elem)
                }
            }
        }
        sqlModelInfo.tableSql = "CREATE TABLE IF NOT EXISTS \(tableName) (id INTEGER PRIMARY KEY AUTOINCREMENT"
        sqlModelInfo.insertSql = "INSERT INTO \(tableName) ("
        sqlModelInfo.updateSql = "INSERT OR REPLACE INTO \(tableName) (id, "
        var insertSufSql : String = ") VALUES (";
        var updateSufSql : String = ") VALUES (mp_sql_replace_id_key, ";
        for (index, elem) in sqlModelInfo.elems.enumerated() {
            sqlModelInfo.tableSql.append(", \(elem.sqlName) \(elem.sqlType.rawValue)")
            if index == 0 {
                sqlModelInfo.insertSql.append("'\(elem.sqlName)'")
                sqlModelInfo.updateSql.append("'\(elem.sqlName)'")
                insertSufSql.append("?");
                updateSufSql.append("?");
            } else {
                sqlModelInfo.insertSql.append(", '\(elem.sqlName)'")
                sqlModelInfo.updateSql.append(", '\(elem.sqlName)'")
                insertSufSql.append(", ?");
                updateSufSql.append(", ?");
            }
        }
        sqlModelInfo.insertSql.append(insertSufSql)
        sqlModelInfo.insertSql.append(")")
        sqlModelInfo.updateSql.append(updateSufSql)
        sqlModelInfo.updateSql.append(")")
        sqlModelInfo.tableSql.append(")")
        /// 如果数据库没有数据，说明没有这个表格，创建表格
        if oldElems.count == 0 {
            db.createTable(sqlModelInfo)
        }
        if addElems.count > 0 {
            db.addColumn(tableName, elems: addElems)
        }
        mp_db_map[tableName] = sqlModelInfo
        mp_convertible_db_lock.unlock()
        return sqlModelInfo
    }
    
    private static func mp_modelInfo() -> MPConvertibleModelInfo? {
        let m = self.init()
        if let displayStyle = Mirror(reflecting: m).displayStyle {
            switch displayStyle {
            case .struct:
                return MPConvertibleMetaData.reloadModelInfo(self, displayStyle: .struct_style, obj: m)
            default:
                return MPConvertibleMetaData.reloadModelInfo(self, displayStyle: .class_style, obj: m)
            }
        }
        return nil
    }
    
}
